﻿IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[esf_sso].[VTreeBasicReferenceDetail]'))
	DROP VIEW [esf_sso].[VTreeBasicReferenceDetail]
GO

-- SELECT * FROM [esf_sso].[VTreeBasicReferenceDetail]

CREATE VIEW [esf_sso].[VTreeBasicReferenceDetail] AS
	
	SELECT
		TBR.ID				Tree_ID
		
		,VP_BR.ID					ParBR_ID
		,VP_BR.Code					ParBR_Code
		,VP_BR.Name					ParBR_Name
		,VP_BR.Description			ParBR_Description
		,VP_BR.Extra_I				ParBR_Extra_I
		,VP_BR.Extra_II				ParBR_Extra_II
		,VP_BR.Extra_III			ParBR_Extra_III
		,VP_BR.IsEnabled			ParBR_IsEnabled
		,VP_BR.CreationDateTime		ParBR_CreationDateTime
		,VP_BR.LastUpdateDateTime	ParBR_LastUpdateDateTime
		,VP_BR.BasicReferenceTypeID	ParBR_BasicReferenceTypeID
		
		,VC_BR.ID					ChiBR_ID
		,VC_BR.Code					ChiBR_Code
		,VC_BR.Name					ChiBR_Name
		,VC_BR.Description			ChiBR_Description
		,VC_BR.Extra_I				ChiBR_Extra_I
		,VC_BR.Extra_II				ChiBR_Extra_II
		,VC_BR.Extra_III			ChiBR_Extra_III
		,VC_BR.IsEnabled			ChiBR_IsEnabled
		,VC_BR.CreationDateTime		ChiBR_CreationDateTime
		,VC_BR.LastUpdateDateTime	ChiBR_LastUpdateDateTime
		,VC_BR.BasicReferenceTypeID	ChiBR_BasicReferenceTypeID
	FROM
		esf_sso.TreeBasicReference TBR
	LEFT JOIN
		esf_sso.VBasicReference VP_BR ON VP_BR.ID = TBR.ParentBasicReferenceID 
	LEFT JOIN
		esf_sso.VBasicReference VC_BR ON VC_BR.ID = TBR.BasicReferenceID 